# pip install openpyxl==3.1.2
from openpyxl import Workbook, load_workbook
from tkinter import filedialog

class excel_tools:
    def __init__(self):
         self.excel_file= filedialog.askopenfilename(title='导入EXCEL', filetypes=[('Excel', '*.xlsx')])

    @property
    def open_excel(self):
        workbook = load_workbook(self.excel_file)
        return workbook

    def read_all(self, sheet_name):
        """
        读取工作表内所有内容
        :param sheet_name: 工作表
        :return:
        """
        wb = self.open_excel
        ws = wb[sheet_name]
        myRows = list(ws.values)[1:]

        all_values = []
        global guihua_tousu_values
        global tousu_dict
        global tousu_values
        global guihua_dics
        global tousu_count
        global site_key
        n=0
        # for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1,max_col=8):
        for myrow in myRows:
            if not myrow[0] is None:
                guihua_tousu_values = []   # 规划，投诉，数据规划成一个列表，作为规划站名（key），对应的值（value）
                guihua_values = []  # 规划站点的经纬度，方位角，规划成一个列表
                tousu_dict={}      # 每一个投诉规划一个字典，投诉名+经纬度
                tousu_values = []    # 所有投诉，规划一个投诉列表
                guihua_dics = {}         # 一个规划站点+n个投诉是一个字典，先置空
                tousu_count = myrow[4]     # 投诉数量
                site_key = myrow[0]            # 规划站名

                guihua_jwd_values=str(myrow[1]) + ',' + str(myrow[2])
                guihua_values.append(guihua_jwd_values)
                guihua_values.append(myrow[3])

                tousu_jwd_values = str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)

                guihua_tousu_values.append(guihua_values)
            else:
                tousu_dict = {}
                n = n + 1
                tousu_jwd_values=str(myrow[6]) + ',' + str(myrow[7])
                tousu_dict[myrow[5]] = tousu_jwd_values
                tousu_values.append(tousu_dict)
                if n+1 == tousu_count:
                    n=0
                    guihua_tousu_values.append(tousu_values)
                    guihua_dics[site_key] = guihua_tousu_values
                    all_values.append(guihua_dics)

        return all_values

if __name__ == '__main__':
    et = excel_tools()
    all_values=et.read_all('数据模板')
    # print(all_values)
    for guihau_site_dics in all_values:
        for guihau_site_name,guihua_values in guihau_site_dics.items():
            print(guihau_site_name)    # 规划名字
            print(guihua_values[0][0])    # 规划经纬度  ['121.338118,28.583169', None]
            print(guihua_values[0][1])  # 规划方位角
            # print(guihua_values[1])  # 投诉字典列表 [{'台州路桥新盛佳苑-A': '121.339109,28.583257'}, {'台州路桥新盛佳苑-B': '121.338116,28.583093'}]
            for tousu_dic in guihua_values[1]:
                for k ,v in tousu_dic.items():
                    print(k)     # 投诉名
                    print(v)    # 投诉经纬度











